package com.chao.common.codeGeneration.generation;

import com.alibaba.fastjson.JSONObject;
import com.chao.common.codeGeneration.bean.Table;
import com.chao.common.codeGeneration.bean.TableColumns;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcDao{

	Connection con;

	// 目标数据库
	String db;

	public String getDb() {
		return db;
	}

	public JdbcDao(String url, String mysqlusername, String password){
		try {
			String driverClassName = "com.mysql.cj.jdbc.Driver";
			Class.forName(driverClassName);
			this.con = DriverManager.getConnection(url, mysqlusername, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public JdbcDao(String url,String mysqlusername,String password,String db){
		try {
			String driverClassName = "com.mysql.cj.jdbc.Driver";
			Class.forName(driverClassName);
			this.con = DriverManager.getConnection(url, mysqlusername, password);
			this.db = db;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void close() {
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<JSONObject> getSql(String sql) {
		List<JSONObject> str = new ArrayList<>();
		try(ResultSet rs = con.prepareStatement(sql).executeQuery()) {
			if(rs == null) {
				return null;
			}
			// 获取列数
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			// 遍历ResultSet中的每条数据
			while (rs.next()) {
				JSONObject jsonObj = new JSONObject();
				// 遍历每一列
				for (int i = 1; i <= columnCount; i++) {
					String columnName =metaData.getColumnLabel(i);
					String value = rs.getString(columnName);
					jsonObj.put(columnName, value);
				}
				str.add(jsonObj);
			}
			return str;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}


	public Table findByUsername(String db, String tb) {

		String sql = "select c.column_name,c.data_type,c.column_comment,c.is_nullable from information_schema.columns c  where c.table_schema ='"+db+"'  and c.table_name = '"+tb+"' ORDER BY c.ORDINAL_POSITION;";
		try(ResultSet rs = con.prepareStatement(sql).executeQuery()) {
			if(rs == null) {
				return null;
			}
			List<TableColumns> ts = new ArrayList<>();
			TableColumns t = null;
			while(rs.next()) {
				if(null == t){
					t = new TableColumns(rs.getString("column_name"),rs.getString("data_type"),rs.getString("column_comment"),"NO".equals(rs.getString("is_nullable")));
					t.setKey(true);
				}else{
					t = new TableColumns(rs.getString("column_name"),rs.getString("data_type"),rs.getString("column_comment"),"NO".equals(rs.getString("is_nullable")));
					t.setKey(false);
				}
				t.setEnd(false);
				ts.add(t);
			}
			t.setEnd(true);
			return new Table(tb,ts);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 查询数据库表结构
	 * @param db
	 * @return
	 */
	public String selectTableStructure(String db,String tableName) {
		String sql = "SHOW CREATE TABLE "+db+"."+tableName+";";
		try(ResultSet rs = con.prepareStatement(sql).executeQuery()) {
			if(rs == null) {
				return null;
			}
			if(rs.next()) {
				return rs.getString("Create Table").replace("\n","");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return null;
	}

	/**
	 * 查询数据库表
	 * @param db
	 * @return
	 */
	public List<String> selectTableName(String db) {
		String sql = "SELECT c.table_name FROM information_schema.tables c WHERE c.table_schema = '"+db+"';";
		try(ResultSet rs = con.prepareStatement(sql).executeQuery()) {
			if(rs == null) {
				return null;
			}
			List<String> ts = new ArrayList<>();
			while(rs.next()) {
				ts.add(rs.getString("table_name"));
			}
			return ts;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 建库建表和写入数据
	 */
	public void createDatabase(String sql) {
		try {
			con.prepareStatement(sql).execute();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
	}
}
